import pandas as pd

import PathUtil


def has_different_pon(group):
    result = []
    for i, row in group.iterrows():
        time = row['故障发生时间']
        start_time = time - pd.Timedelta(minutes=10)
        end_time = time + pd.Timedelta(minutes=10)
        # 筛选出同一 ip 在十分钟时间窗口内的数据
        window = group[(group['故障发生时间'] >= start_time) & (group['故障发生时间'] < end_time)]
        # 判断 pon 是否有不同的值
        result.append(len(window[port_col].unique()) > 1)
    return pd.Series(result, index=group.index)


if __name__ == '__main__':
    ip_col = '传输设备ip'
    port_col = '传输设备端口2'

    file_paths = [
        r"D:\Download\WeChat Files\wxid_kdchbeq2xllp22\FileStorage\File\2025-06\1751277268498.xlsx"
    ]
    dfs = []
    for file_path in file_paths:
        df = pd.read_excel(file_path)
        required_columns = ['流水号', '工单状态',
                            '告警名称', '告警描述', '故障发生时间', ip_col, '传输设备端口']
        missing_columns = [col for col in required_columns if col not in df.columns]
        if missing_columns:
            print(f'文件 {file_path} 缺少以下列: {missing_columns}')
            continue
        df = df[required_columns]
        dfs.append(df)
    df = pd.concat(dfs, ignore_index=True)
    df['故障发生时间'] = pd.to_datetime(df['故障发生时间'])
    # start_date = pd.to_datetime('2025-04-26')
    # end_date = pd.to_datetime('2025-05-17')
    # df = df[(df['故障发生时间'] >= start_date) & (df['故障发生时间'] <= end_date)]
    df = df[df['告警名称'] != '告警名称']
    # df = df.drop_duplicates()

    t = pd.read_csv(PathUtil.olt_bras(), usecols=['OLT_IP', 'BRAS1_ip'])
    t.rename(columns={
        'OLT_IP': ip_col
    }, inplace=True)
    df = df.merge(t, on=ip_col, how='left')

    types = pd.read_csv(
        "D:\\家宽\\source\\t_rules_alarm_category.csv")

    types['告警名'] = types['告警名'].str.replace(r'\(\d+\)', '', regex=True)
    uplink = types[types['分段'] == '上联']['告警名']
    tuifu = types[types['类型'] == 'OLT']['告警名']
    fenzhi = types[types['分段'] == '分支']['告警名']

    df[port_col] = df['传输设备端口'].apply(
        lambda x: '-'.join(str(x).split('-')[-4:]) if pd.notna(x) else "")
    # 直接在df上统计当前行十分钟内同一BRAS1_ip但oltip不同的个数
    df['分类'] = None
    for index, row in df.iterrows():
        # 先检查当前告警是否属于uplink或tuifu
        if row['告警名称'] in uplink.values or row['告警名称'] in tuifu.values:
            # 只对uplink/tuifu类型的告警执行统计逻辑
            time_window = (df['故障发生时间'] >= row['故障发生时间'] - pd.Timedelta(minutes=10)) & (
                    df['故障发生时间'] <= row['故障发生时间'] + pd.Timedelta(minutes=10))
            same_bras = df['BRAS1_ip'] == row['BRAS1_ip']
            is_uplink_tuifu = df['告警名称'].isin(uplink) | df['告警名称'].isin(tuifu)
            unique_oltips = df[time_window & same_bras & is_uplink_tuifu]['传输设备ip'].unique()
            count = len(unique_oltips)
            if count == 1:
                df.at[index, '分类'] = '单olt'
            elif 1 < count <= 10:
                df.at[index, '分类'] = '多olt'
            elif count > 10:
                df.at[index, '分类'] = '单bras'

    df['多PON口'] = df.groupby(ip_col)[[port_col, '故障发生时间']].apply(has_different_pon).reset_index(0,
                                                                                                        drop=True)
    df['多PON口'] = df['多PON口'].astype(bool)

    df.loc[df['分类'].isnull() & df['多PON口'], '分类'] = '多PON口'
    df.loc[df['分类'].isnull() & ~df['多PON口'], '分类'] = '单PON口'
    print('df[\'分类\']的各种分类的个数：')
    print(df['分类'].value_counts())

    df.to_excel('/temp/故障类别.xlsx', index=False)
